﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBAccessLibrary.Interface;
using DBAccessLibrary.Model;
using DataAccess;

namespace DBAccessLibrary.Grammar
{
    class OracleGrammar : DBGrammar, IGrammar, ILocalGrammar
    {
        public OracleGrammar(ConnectionModel connect)
            : base(connect)
        { }

        private string GetTypeFilter(FieldEntity field, string sql)
        {
            string typeStr = field.TypeName;
            int columnLength;
            int.TryParse(field.Length, out columnLength);
            string length = field.Length;
            string TypeName = field.TypeName.ToUpper();
          
                        if (TypeName == "VARCHAR2" || TypeName == "VARCHAR")
                        {
                            typeStr = "VARCHAR2";
                            length = "(" + field.Length + ")";
                        }
                        if (TypeName == "INTEGER" || TypeName == "SMALLINT")
                        {
                            length = "";
                        }
                        if (TypeName == "NUMBER")
                        {
                            length = "(" + field.Length + ")";
                        }
                

            if (TypeName == "TIMESTAMP" || TypeName == "DATE")
            {
                length = "";
            }
            return string.Format(sql, field.TableName, field.Name, typeStr, length);
        }
        #region IGrammar 成员

        public string AddColumn(Model.FieldEntity field)
        {
            string sql = "ALTER TABLE {0} ADD {1} {2}{3}";

            if (!field.IsNull)
            {
                sql += " Not Null";
            }
            if (!string.IsNullOrEmpty(field.DefaultValue))
            {
                sql += " DEFAULT " + field.DefaultValue;
            }
            return GetTypeFilter(field, sql);
        }

        public IEnumerable<string> EditColumn(Model.FieldEntity field)
        {
            List<string> sql = new List<string>();

            string text = "ALTER TABLE {0} MODIFY {1} {2}{3}";
            if (field.IsChange(o=>o.IsNull) )
            {
                if (field.IsNull)
                { text += " Null"; }
                else
                { text += " Not Null";}
            }

            sql.Add(GetTypeFilter(field, text));


            if (!string.IsNullOrEmpty(field.DefaultValue))
            {

            }
            return sql;
        }

        public IEnumerable<string> RemoveColumn(Model.FieldEntity field)
        {
            List<string> list = new List<string>();
            list.Add(string.Format("ALTER TABLE {0} DROP COLUMN {1}", field.TableName, field.TransName));
            return list;
        }

        public IEnumerable<string> EditPrimary(Model.TableEntity table)
        {
            List<string> sql = new List<string>();
            string pk = table.PrimaryKeyName;
            string[] parimaryKey = table.FileNodes.Where(o => o.IsPrimaryKey).Select(o => o.Name).ToArray();
            if (parimaryKey.Length > 0)
            {
                //删除约束
                if (!string.IsNullOrEmpty(pk))
                {
                    string drop = string.Format("alter table {0} drop constraint {1} cascade", table.Name, pk);
                    sql.Add(drop);
                }
                else
                {
                    pk = "PK" + parimaryKey[0];
                }
                sql.Add(string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY({2})",
                    table.Name,
                    pk,
                    string.Join(",", parimaryKey)));
            }
            return sql;
        }

        public string SelectTop(Model.TableEntity table, int count)
        {
            string sql =  "SELECT * FROM {0} WHERE ROWNUM <= {1}";

            return string.Format(sql, table.TransName, count);
        }

        public string GetChTableSql
        {
            get
            {
                if (string.IsNullOrEmpty(_xml.ChDescription))
                {
                    return
                        "SELECT TABLENAME,CHINESETABLENAME as CHINATABLENAME,FIELDNAME as COLUMNNAME,CHINESENAME as CHINACOLUMNNAME " +
                        "FROM T_FIELD_DICTIONARY order by TABLENAME";
                }
                return _xml.ChDescription;
            }
        }

        public string IsExistChTableSql
        {
            get 
            {
                return "select table_name from user_tables where table_name='T_FIELD_DICTIONARY'";  
            }
        }

        public string AppendTabText(string text)
        {
            return base.TabText(text);
        }

        public string GetErrorInfo(string errorMsg)
        {
            return string.Empty;
        }

        public string GetProcParamInfo(Model.ProcEntity entity)
        {
            if (entity == null)
                return string.Empty;
            string sql;
            if (entity.ChildNodes.Count > 0)
            {
                List<string> list = new List<string>();
                foreach (ProcParamEntity param in entity.ChildNodes)
                {
                    if (param.Status == "IN")
                    {
                        list.Add(string.Format("{0}[{1}({2})]", Environment.NewLine, param.Name, param.TypeName));
                    }
                    else
                    {
                        list.Add(string.Format("{0}[{1}({2})-OUT]", Environment.NewLine, param.Name, param.TypeName));
                    }
                }
                sql = "(" + string.Join(",", list.ToArray()) + ")";
            }
            else
            {
                sql = "()";
            }
            return string.Format("{0} {1}{2}",
                                 "EXEC",
                                 entity.Name,
                                 sql);
        }

        public string GetPageingSql(string sql, decimal index, decimal count)
        {
            string newSql = "SELECT tab.* FROM (SELECT {0} ROWNUMBER,t.* FROM (" + Environment.NewLine + "{1}" + Environment.NewLine + ") t) tab  WHERE tab.ROWNUMBER >= " + index + " and tab.ROWNUMBER < " + count;

            newSql = string.Format(newSql, "rownum", sql);

            return newSql;
        }
        protected override string GetRowNumUpdate(string tableName, string where, string column, string rownumber)
        {

            return string.Format("UPDATE ({0}) T1 SET {1} WHERE ROWNUM={2}",
                                "SELECT * FROM " + tableName + " " + where,
                                column,
                                rownumber);

        }
        protected override string GetRowNumDelete(string tableName, string where, string rownumber)
        {
            return string.Format("DELETE FROM ({0}) T1 WHERE ROWNUM={1}",
                                "SELECT * FROM " + tableName + " " + where,
                                rownumber);

        }
        #endregion

        #region ILocalGrammar 成员

        public DataAccess.DatabaseType GetConnectionStr(out string connectStr)
        {
            connectStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={3})(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={0})));User Id={1};Password={2};Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Timeout=60;Connection Lifetime=0;Persist Security Info=true ";

            connectStr = string.Format(connectStr,
                                             _connect.DataBase,
                                             _connect.UserID,
                                             _connect.PassWord,
                                             _connect.Server);

            return DatabaseType.Oracle;

        }

        #endregion
    }
}
